/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package test.db_work;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import onpu_dms_xls.exceptions.MyException;
import onpu_dms_xls.kernel.common.DB.DBConnect;

/**
 *
 * @author snbm
 */
public class old_new_DB_WORK {

    private static List<Kafedra_speciality> kaf_specs =
            new ArrayList<Kafedra_speciality>();

    public static void main(String... args) {
        getDataFromOLD_DB();
        //printAllKafedraSpecialities();
        createQueriesToNewDB();
    }

    private static void getDataFromOLD_DB() {
        Connection con = null;
        try {
            DBConnect dbc = new DBConnect("jdbc:postgresql:postgres", "postgres", "1111");
            con = dbc.getConnection();
        } catch (MyException e) {
            System.out.println(e);
            return;
        }

        //get all kafedra_specialities, kafedra names, speciality names
        try {
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select ks.\"кафедра_спец_ть_id\", ks.\"кафедра_id\", ks.\"спец_ть_id\", sp.\"название\" as spec_name from \"кафедра_спец_ть\" ks, \"специальности\" sp where sp.\"код\" = ks.\"спец_ть_id\" order by \"кафедра_спец_ть_id\"");
            while (rs.next()) {
                Kafedra_speciality ks = new Kafedra_speciality();
                ks.OLD_kafedra_speciality_id = rs.getInt("кафедра_спец_ть_id");
                ks.kafedra_id = rs.getInt("кафедра_id");
                ks.OLD_speciality_id = rs.getInt("спец_ть_id");
                ks.setOldSpecialityName(rs.getString("spec_name"));
                kaf_specs.add(ks);
            }
        } catch (SQLException e) {
            System.out.println(e.getSQLState());
            System.out.println(e.getMessage());
            return;
        }



        //get all info for speciality_extract
        for (Kafedra_speciality ks : kaf_specs) {
            try {
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("SELECT vip_spec.\"учебн_нагр\" as study_load, vip_spec.\"период_практики\" as practice_period, "
                        + "vip_spec.\"гос_экзамен\" as gos_full_name, vip_spec.\"период_выполнения\" as thesis_execution_time, "
                        + "vip_spec.\"вып_серия\" as extract_series, vip_spec.\"всего_часов\" as hours_total "
                        + "FROM \"выписка_специальность\" vip_spec, \"кафедра_спец_ть\" kaf_spec "
                        + "WHERE kaf_spec.\"кафедра_спец_ть_id\"=(" + ks.OLD_kafedra_speciality_id + ") and kaf_spec.\"спец_ть_id\" = vip_spec.\"код\";");
                while (rs.next()) {
                    Speciality_extract se = new Speciality_extract();
                    se.setStudy_load(rs.getString("study_load"));
                    se.setPractice_period(rs.getString("practice_period"));
                    se.setGos_full_name(rs.getString("gos_full_name"));
                    se.setThesis_execution_time(rs.getString("thesis_execution_time"));
                    se.setExtract_series(rs.getString("extract_series"));
                    se.setHours_total(rs.getInt("hours_total"));
                    se.setSpeciality_specialization_id(1);
                    se.setThesis_type_id(1);
                    ks.setSe(se);
                }
            } catch (SQLException e) {
                System.out.println(e.getSQLState());
                System.out.println(e.getMessage());
                return;
            }
        }

        //get all info for speciality_qualification
        for (Kafedra_speciality ks : kaf_specs) {
            try {
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("SELECT spec_qual.\"квалификация\" as qualification_full, spec_qual.\"тип_программы\" as program_type, "
                        + "spec_qual.\"академ_права\" as akadem_rights, spec_qual.\"проф_права\" as profeccional_rights, "
                        + "spec_qual.\"название_дипломa\" as diploma_name, spec_qual.\"срок_обуч\" as study_period "
                        + "FROM \"специальность_квалификация\" spec_qual, \"кафедра_спец_ть\" k "
                        + "WHERE k.\"кафедра_спец_ть_id\"=(" + ks.OLD_kafedra_speciality_id + ") and k.\"спец_ть_id\" = spec_qual.\"код\";");
                while (rs.next()) {
                    Speciality_qualification sq = new Speciality_qualification();
                    sq.setQualification_full(rs.getString("qualification_full"));
                    sq.setProgram_type(rs.getString("program_type"));
                    sq.setAkadem_rights(rs.getString("akadem_rights"));
                    sq.setProfeccional_rights(rs.getString("profeccional_rights"));
                    sq.setDiploma_name(rs.getString("diploma_name"));
                    sq.setStudy_period(rs.getString("study_period"));
                    ks.setSq(sq);
                }
            } catch (SQLException e) {
                System.out.println(e.getSQLState());
                System.out.println(e.getMessage());
                return;
            }
        }
    }

    private static void printAllKafedraSpecialities(){
        for (Kafedra_speciality ks : kaf_specs) {
            //System.out.println(ks);
            System.out.println("________________");
            System.out.println("kaf_spec_id: "+ks.OLD_kafedra_speciality_id);
            System.out.println("kafedra: id=" + ks.kafedra_id);
            System.out.println("speciality: id=" + ks.OLD_speciality_id + " name=" + ks.getOldSpecialityName());
            System.out.println("qualification_full: " + ks.getSq().getQualification_full());
        }
    }

    private static void createQueriesToNewDB(){
        //createQueriesForKS
        for (Kafedra_speciality ks : kaf_specs){
            System.out.println(ks.make_INSERT_into_kafedra_speciality());
        }

        //createQueryForSQ
        for (Kafedra_speciality ks : kaf_specs){
            System.out.println(ks.getSq().make_INSERT_into_speciality_qualification(ks.OLD_kafedra_speciality_id));
        }

        //createQueryForSE
        for (Kafedra_speciality ks : kaf_specs){
            System.out.println(ks.getSe().make_INSERT_into_speciality_extract(ks.OLD_kafedra_speciality_id));
        }
    }
}
